package com.service.imp;

import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.common.uilt.CalculationTool;
import com.common.uilt.ConfigUtil;
import com.common.uilt.DateUtil;
import com.common.vo.Pageutil;
import com.dao.AssetsDao;
import com.dao.AssetsDetailsDao;
import com.dao.DeptDao;
import com.dao.MeasureDao;
import com.dao.ReceiptDao;
import com.dao.ReceiptDetailsDao;
import com.dao.SuppliersDao;
import com.model.AssetsBean;
import com.model.AssetsDetailsBean;
import com.model.Messages;
import com.model.ReceiptBean;
import com.model.ReceiptDetailsBean;
import com.model.SuppliersBean;
import com.model.UserBean;

@Service
@Transactional(rollbackFor = { RuntimeException.class }, propagation = Propagation.REQUIRED)
public class AssetsService {
	@Autowired
	private MeasureDao measureDao;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private SuppliersDao suppliersDao;
	@Autowired
	private AssetsDao assetsDao;
	@Autowired
	private DeptDao deptDao;
	@Autowired
	private AssetsDetailsDao assetsDetailsDao;
	@Autowired
	private ReceiptDetailsDao receiptDetalisDao;
	@Autowired
	private ReceiptDao receiptDao;
	
	public void sevaAll(ReceiptBean receiptBean,List<AssetsBean> list, String path,HttpSession session) throws CloneNotSupportedException {
		String head = ConfigUtil.getFileIo("serial.head", path);
		String interval = ConfigUtil.getFileIo("serial.interval", path);
		Long no = Long.parseLong(ConfigUtil.getFileIo("serial.no", path));
		String is_auto_number = ConfigUtil.getFileIo("is_auto_number", path);
		UserBean u=(UserBean)session.getAttribute("user");
		receiptBean.setReceipt_type("入库单");
		this.receiptDao.save(receiptBean);//保存单据
		Double count_je=0.00d;//统计金额
		Set<ReceiptDetailsBean> receiptSet= new HashSet<ReceiptDetailsBean>();
		ReceiptDetailsBean rdb= null;
		if ("1".equals(is_auto_number)) {//如果是自动编号
			int count = 0;
			for (AssetsBean ab : list) {
				SuppliersBean sb=null;
				String a_img_src = ab.getA_img_src()!=null&&!"".equals(ab.getA_img_src())?"/imgtemp/"+ab.getA_img_src():"";
				//System.out.println("图片"+a_img_src);
				try {
					sb = this.suppliersDao.get(ab.getSuppliers_id());
					ab.setA_suppliers(sb);
				} catch (NullPointerException e) {
					System.out.println("没有设置id，需要根据suppliers来插入一条数据");
				}
				ab.setA_img_src(a_img_src);
				ab.setA_register_data(ab.getA_register_data() != null&&!"".equals(ab.getA_register_data()) ?ab.getA_register_data():DateUtil.SD2
						.format(new Date()) );
				//月折旧
				ab.setA_ytd(CalculationTool.getMD(ab.getA_jc_rate(),
						ab.getA_years(), ab.getA_unit_price()));
				ab.setA_count_d(0.00);
				//净值=单价-单价*残值率
				ab.setA_j_value(ab.getA_unit_price()-CalculationTool.getJc(ab.getA_unit_price(),ab.getA_jc_rate()));
				for (int i = 1; i <= ab.getNumber(); i++) {
					AssetsBean assetsBean = (AssetsBean) ab.clone();
					assetsBean.setA_number(head+interval+(no+count));
					this.assetsDao.save(assetsBean);
					count++;
					count_je+=assetsBean.getA_unit_price();
				}
				rdb= new ReceiptDetailsBean();
				rdb.setReceipt_id(receiptBean);
				rdb.setAssets_class_name(ab.getA_class_name());
				rdb.setAssets_name(ab.getA_name());
				rdb.setAssets_num(ab.getNumber());
				rdb.setAssets_money(ab.getA_unit_price()+"");
				rdb.setAssets_specification(ab.getA_specification());
				this.receiptDetalisDao.save(rdb);
				receiptSet.add(rdb);
			}
			ConfigUtil.setprop("serial.no", no+count+"", path,u.getUser_account());
		} else {
			for (AssetsBean ab : list) {
				SuppliersBean sb=null;
				String a_img_src = ab.getA_img_src()==null?"无":"/imgtemp/"+ab.getA_img_src();
				try {
					sb = this.suppliersDao.get(ab.getSuppliers_id());
					ab.setA_suppliers(sb);
				} catch (NullPointerException e) {
					System.out.println("没有设置id，需要根据suppliers来插入一条数据");
				}
				ab.setA_img_src(a_img_src);
				ab.setA_register_data(ab.getA_register_data() == null ? DateUtil.SD2
					.format(new Date()) : ab.getA_register_data());
				//月折旧
				ab.setA_ytd(CalculationTool.getMD(ab.getA_jc_rate(),
						ab.getA_years(), ab.getA_unit_price()));
				ab.setA_count_d(0.00);
				ab.setA_j_value(ab.getA_unit_price()-CalculationTool.getJc(ab.getA_unit_price(),ab.getA_jc_rate()));
				this.assetsDao.save(ab);
				count_je+=ab.getA_unit_price();
				
				rdb= new ReceiptDetailsBean();
				rdb.setReceipt_id(receiptBean);
				rdb.setAssets_class_name(ab.getA_class_name());
				rdb.setAssets_name(ab.getA_name());
				rdb.setAssets_num(ab.getNumber());
				rdb.setAssets_money(ab.getA_unit_price()+"");
				rdb.setAssets_specification(ab.getA_specification());
				rdb.setAssets_number(ab.getA_number());
				this.receiptDetalisDao.save(rdb);
				receiptSet.add(rdb);
			}
		}
		/**
		 * 产生入库单
		 */
		receiptBean.setReceipt_money(count_je);
		receiptBean.setReceiptdetails_list(receiptSet);
		this.receiptDao.update(receiptBean);
		
		
	}
	/**
	 * 获取闲置全部资产
	 * @param currentPage
	 * @param sortField
	 * @param orderType
	 * @param maxrecord
	 * @param request
	 */
	public void getAssetsBystatus(int currentPage, String sortField,
			String orderType, int maxrecord, HttpServletRequest request) {
		if (currentPage <= 0) 
			currentPage = 1;
		if (maxrecord <= 0)
			maxrecord = 10;
	
		
		StringBuffer sqlcont = new StringBuffer();
		sqlcont.append("select count(a.id) from tb_assets a where 1=1 ");
		String select_number =request.getParameter("select_number");
		
		String not_number=request.getParameter("not_number");
		String search_status =request.getParameter("search_status");
		String search_class_name =request.getParameter("search_class_name");
		String search_name =request.getParameter("search_name"); 
		String search_specification =request.getParameter("search_specification");
		String search_measure =request.getParameter("search_measure");
		String search_source =request.getParameter("search_source");
		String search_buy_date_go =request.getParameter("search_buy_date_go");
		String search_buy_date_end =request.getParameter("search_buy_date_end");
		String search_register_data_go =request.getParameter("search_register_data_go");
		String search_register_data_end =request.getParameter("search_register_data_end");
		String search_unit_price_go =request.getParameter("search_unit_price_go");
		String search_unit_price_end =request.getParameter("search_unit_price_end");
		String search_years_go =request.getParameter("search_years_go");
		String search_years_end =request.getParameter("search_years_end");
		String search_f_number =request.getParameter("search_f_number");
		String search_machine_no =request.getParameter("search_machine_no");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (");
		sql.append("select rownum rn, b.* from");
		sql.append("(select a.* from tb_assets a where 1=1 ");
		/**
		 * 编号
		 */
		if(isNull(select_number)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_number like '%");
			sqlcont.append(select_number);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_number like '%");
			sql.append(select_number);
			sql.append("%'");
			request.setAttribute("select_number", select_number);
		}
		if(not_number!=null&&!"".equals(not_number)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_number not in('");
			sqlcont.append(not_number);
			sqlcont.append("')");
			
			sql.append(" and");
			sql.append(" a.a_number not in('");
			sql.append(not_number);
			sql.append("')");
			request.setAttribute("not_number", not_number);
		}
		
		/**
		 * 状态
		 */
		if(isNull(search_status)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_status like '%");
			sqlcont.append(search_status);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_status like '%");
			sql.append(search_status);
			sql.append("%'");
			request.setAttribute("search_status", search_status);
		}else{
			sqlcont.append(" and");
			sqlcont.append(" a.a_status like '%");
			sqlcont.append("闲置");
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_status like '%");
			sql.append("闲置");
			sql.append("%'");
			request.setAttribute("search_status", search_status);
		}
		
		/**
		 * 分类
		 */
		if(isNull(search_class_name)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_class_name like '%");
			sqlcont.append(search_class_name);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_class_name like '%");
			sql.append(search_class_name);
			sql.append("%'");
			request.setAttribute("search_class_name", search_class_name);
		}
		/**
		 * 名称
		 */
		if(isNull(search_name)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_name like '%");
			sqlcont.append(search_name);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_name like '%");
			sql.append(search_name);
			sql.append("%'");
			request.setAttribute("search_name", search_name);
		}
		/*
		 * 规格
		 */
		if(isNull(search_specification)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_specification like '%");
			sqlcont.append(search_specification);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_specification like '%");
			sql.append(search_specification);
			sql.append("%'");
			request.setAttribute("search_specification", search_specification);
		}
		/*
		 * 计量单位
		 */
		if(isNull(search_measure)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_measure like '%");
			sqlcont.append(search_measure);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_measure like '%");
			sql.append(search_measure);
			sql.append("%'");
			request.setAttribute("search_measure", search_measure);
		}
		/*
		 * 来源
		 */
		if(isNull(search_source)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_source like '%");
			sqlcont.append(search_source);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_source like '%");
			sql.append(search_source);
			sql.append("%'");
			request.setAttribute("search_source", search_source);
		}
		/*
		 * 购买时间开始
		 * 
		 */
		if(isNull(search_buy_date_go)){
			sqlcont.append(" and");
			sqlcont.append(" to_date(a.a_buy_date,'yyyy-mm-dd') >= to_date('");
			sqlcont.append(search_buy_date_go);
			sqlcont.append("','yyyy-mm-dd')");
			
			sql.append(" and");
			sql.append(" to_date(a.a_buy_date,'yyyy-mm-dd') >= to_date('");
			sql.append(search_buy_date_go);
			sql.append("','yyyy-mm-dd')");
			request.setAttribute("search_buy_date_go", search_buy_date_go);
		}
		/*
		 * 购买时间结束
		 *
		 */
		if(isNull(search_buy_date_end)){
			sqlcont.append(" and");
			sqlcont.append(" to_date(a.a_buy_date,'yyyy-mm-dd') <= to_date('");
			sqlcont.append(search_buy_date_end);
			sqlcont.append("','yyyy-mm-dd')");
			
			sql.append(" and");
			sql.append(" to_date(a.a_buy_date,'yyyy-mm-dd') <= to_date('");
			sql.append(search_buy_date_end);
			sql.append("','yyyy-mm-dd')");
			request.setAttribute("search_buy_date_end", search_buy_date_end);
		}
		/*
		 *登记时间开始
		 *to_date(a.a_register_data,'yyyy-mm-dd')
		 */
		if(isNull(search_register_data_go)){
			sqlcont.append(" and");
			sqlcont.append(" to_date(a.a_register_data,'yyyy-mm-dd') >= to_date('");
			sqlcont.append(search_register_data_go);
			sqlcont.append("','yyyy-mm-dd')");
			
			sql.append(" and");
			sql.append(" to_date(a.a_register_data,'yyyy-mm-dd') >= to_date('");
			sql.append(search_register_data_go);
			sql.append("','yyyy-mm-dd')");
			request.setAttribute("search_register_data_go", search_register_data_go);
		}
		/*
		 * 登记时间结束
		 * 
		 */
		if(isNull(search_register_data_end)){
			sqlcont.append(" and");
			sqlcont.append(" to_date(a.a_register_data,'yyyy-mm-dd') <= to_date('");
			sqlcont.append(search_register_data_end);
			sqlcont.append("','yyyy-mm-dd')");
			
			sql.append(" and");
			sql.append(" to_date(a.a_register_data,'yyyy-mm-dd') <= to_date('");
			sql.append(search_register_data_end);
			sql.append("','yyyy-mm-dd')");
			request.setAttribute("search_register_data_end", search_register_data_end);
		}
		
		/*
		 *单价区间开始
		 *
		 */
		if(isNull(search_unit_price_go)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_unit_price >= ");
			sqlcont.append(search_unit_price_go);

			
			sql.append(" and");
			sql.append(" a.a_unit_price>= ");
			sql.append(search_unit_price_go);

			request.setAttribute("search_unit_price_go", search_unit_price_go);
		}
		/*
		 * 单价区间结束
		 * 
		 */
		if(isNull(search_unit_price_end)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_unit_price <= ");
			sqlcont.append(search_unit_price_end);
			
			sql.append(" and");
			sql.append(" a.a_unit_price <= ");
			sql.append(search_unit_price_end);

			request.setAttribute("search_unit_price_end", search_unit_price_end);
		}
		
		/*
		 *年份区间开始
		 *
		 */
		if(isNull(search_years_go)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_years >= ");
			sqlcont.append(search_years_go);

			
			sql.append(" and");
			sql.append(" a.a_years>= ");
			sql.append(search_years_go);

			request.setAttribute("search_years_go", search_years_go);
		}
		/*
		 * 年份区间结束
		 * 
		 */
		if(isNull(search_years_end)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_years <= ");
			sqlcont.append(search_years_end);
			
			sql.append(" and");
			sql.append(" a.a_years <= ");
			sql.append(search_years_end);
			request.setAttribute("search_years_end", search_years_end);
		}
		
		
		/*
		 *财务编码
		 * 
		 */
		if(isNull(search_f_number)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_f_number like '%");
			sqlcont.append(search_f_number);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_f_number like '%");
			sql.append(search_f_number);
			sql.append("%'");
			request.setAttribute("search_f_number", search_f_number);
		}
		
		/*
		 *机器序号
		 * 
		 */
		if(isNull(search_machine_no)){
			sqlcont.append(" and");
			sqlcont.append(" a.a_machine_no like '%");
			sqlcont.append(search_machine_no);
			sqlcont.append("%'");
			
			sql.append(" and");
			sql.append(" a.a_machine_no like '%");
			sql.append(search_machine_no);
			sql.append("%'");
			request.setAttribute("search_machine_no", search_machine_no);
		}
		//排序
		if (sortField != null && !"".equals(sortField)) {
			sql.append(" order by a.");
			sql.append(sortField);
			sql.append(" " + orderType);
		}else{
			sql.append(" order by a.a_number asc");
		}
		sql.append(") b");
		sql.append(" where rownum <= ");
		sql.append(currentPage * maxrecord);
		sql.append(") t");
		sql.append(" where t.rn >=");
		sql.append((currentPage - 1) * maxrecord + 1);
		System.out.println(sql);
		List<Map<String, Object>> l = this.jdbcTemplate.queryForList(sql.toString());
		request.setAttribute("Pageutil", new Pageutil(currentPage, maxrecord,
				this.jdbcTemplate.queryForObject(sqlcont.toString(),Integer.class), sortField,
				orderType));
		request.setAttribute("assetsList", l);
	}
	private boolean isNull(String str){
		return null!=str&&!"".equals(str);
	}

	/**
	 * 获取全部在用资产
	 * 
	 */
	public List<Map<String, Object>> getAssetsByfield(String fieldName,String name){
//		Messages m= new Messages();
		StringBuffer sb= new StringBuffer();
		sb.append("select a.id ");
		sb.append(",a.a_number");
		sb.append(",a.a_class_name");
		sb.append(",a.a_specification");
		sb.append(",a.a_name");
		sb.append(",a.a_measure");
		sb.append(",a.a_status");
		sb.append(",a.a_custody_staff");
		sb.append(",a.a_unit_price");
		sb.append(",a.a_years");
		sb.append(",a.a_jc_rate");
		sb.append(",a.a_d_m");
		sb.append(",a.a_ytd");
		sb.append(",a.a_count_d");
		sb.append(",a.a_j_value");
		sb.append(",a.a_buy_date");
		sb.append(",a.a_register_data");
		sb.append(",(select d.dept_name from tb_dept d where a.a_use_dept=d.id) as dept_name");
		/**
		 * 查询出上次计提时间 与当前时间月份进行比较，当前时间月份大于上次计提时间才算没有计提
		 * 计提后已出库的单据不可修改，只能退库
		 */
		sb.append(",case when a.id in(select e.assets_id from tb_assets_details e where ");
		sb.append("to_char(to_date(e.detalis_date,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')) then 1 ");
		sb.append(" else 0 end as isdepreciation");
		sb.append(" from tb_assets a where 1=1 and a.a_status='在用'");
		if(isNull(fieldName)){
			sb.append(" and a.");
			sb.append(fieldName);
			sb.append(" like '%");
			sb.append(name);
			sb.append("%'");
		}
		sb.append(" order by dept_name asc");
//		m.setInfo(true);
		System.out.println(sb.toString());
		return this.jdbcTemplate.queryForList(sb.toString());
				
	}
	/**
	 * 
	 * @return 需要报废的资产数量
	 */
	public Messages fold() {
		//String hql = "from AssetsBean a where a.a_status=? not in ()";
		Messages m= new Messages();
		StringBuffer hql = new StringBuffer();
//		hql.append("select b.* from tb_assets b  where b.a_status='在用' and b.a_number  in (");
//		hql.append(" select a.assets_number");
//		hql.append(" from tb_receipt t");
//		hql.append(" left join tb_receipt_details a on a.receipt_id = t.id");
//		hql.append(" where to_char(to_date(t.receipt_data,'yyyy-mm-dd'),'mm')=to_char(sysdate,'mm')");
//		hql.append(")");
		//查询出非当月出库的资产 并且当还未进行过折旧处理的资产进行折旧
		hql.append("select b from AssetsBean b  where b.a_status=? and b.a_number not in (");
		//查询出非当月出库的资产
		hql.append(" select a.assets_number");
		hql.append(" from ReceiptDetailsBean a");
		hql.append(" left join a.receipt_id as t");
		hql.append(" where to_char(to_date(t.receipt_data,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')");
		hql.append(" and t.receipt_type='出库单') and b.id not in (");
		//查询出当前月份所有已折旧的资产
		hql.append("select e.assets_id from AssetsDetailsBean e where");
		hql.append(" to_char(to_date(e.detalis_date,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')");
		hql.append(")");
		
		//System.out.println(hql.toString());
		//hibernateTemplate 方式
		List<AssetsBean> list =this.assetsDao.find(hql.toString(),"在用");
		//jdbc 方式
		//List<AssetsBean> list = (List<AssetsBean>)this.jdbcTemplate.queryForList(hql.toString(),AssetsBean.class);
		/*
		 * 报废规则
		 * 如果账面净值 为0 或者<=年份*12
		 */
		//List<AssetsBean> bflist=new ArrayList<AssetsBean>();//需要报废的
		int count=0;
		for(AssetsBean assetsBean:list){
			AssetsDetailsBean ad= new AssetsDetailsBean();
			ad.setAssets_id(assetsBean);//资产id
			ad.setDept_id(assetsBean.getA_use_dept().getId());//部门
			ad.setDetalis_date(DateUtil.SD2.format(new Date()));//折旧时间
			ad.setAssets_class(assetsBean.getA_class_name());//资产分类
			ad.setAssets_value(assetsBean.getA_unit_price());
			ad.setAd_ytd(assetsBean.getA_ytd());//月提折旧 
			ad.setAd_count_d(assetsBean.getA_count_d()+assetsBean.getA_ytd());//累计
			ad.setAd_jc(CalculationTool.getJc(assetsBean.getA_unit_price(), assetsBean.getA_jc_rate()));//残值
			ad.setAd_jz(assetsBean.getA_j_value()-assetsBean.getA_ytd());
			if(ad.getAd_jz()<=assetsBean.getA_years()*12){
				count++;
			}
			//累计折旧
			assetsBean.setA_count_d(assetsBean.getA_count_d()+assetsBean.getA_ytd());
			//净值
			assetsBean.setA_j_value(assetsBean.getA_j_value()-assetsBean.getA_ytd());
			this.assetsDetailsDao.save(ad);//关联映射自动保存assetsBean
		}
		
		//查询出当月出库的资产进行折旧
		hql=new StringBuffer();
		hql.append("select b from AssetsBean b  where b.a_status=? and b.a_number in (");
		hql.append(" select a.assets_number");
		hql.append(" from ReceiptDetailsBean a");
		hql.append(" left join a.receipt_id as t");
		hql.append(" where to_char(to_date(t.receipt_data,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')");
		hql.append(" and t.receipt_type='出库单' ")
			.append(")");
		//and 没有进行过折旧的资产
		hql.append(" and b.id not in (select e.assets_id from AssetsDetailsBean e where ");
		hql.append(" to_char(to_date(e.detalis_date,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')")
			.append(")");
		List<AssetsBean> listin =this.assetsDao.find(hql.toString(),"在用");
		for(AssetsBean assetsBean:listin){
			AssetsDetailsBean ad= new AssetsDetailsBean();
			ad.setAssets_id(assetsBean);//资产id
			ad.setDept_id(assetsBean.getA_use_dept().getId());//部门
			ad.setDetalis_date(DateUtil.SD2.format(new Date()));//折旧时间
			ad.setAssets_class(assetsBean.getA_class_name());//资产分类
			ad.setAssets_value(assetsBean.getA_unit_price());
			ad.setAd_ytd(assetsBean.getA_ytd());//月提折旧 
			ad.setAd_count_d(assetsBean.getA_count_d());//累计
			ad.setAd_jc(CalculationTool.getJc(assetsBean.getA_unit_price(), assetsBean.getA_jc_rate()));//残值
			ad.setAd_jz(assetsBean.getA_j_value());
			this.assetsDetailsDao.save(ad);
		}
		m.setInfo(true);
		m.setText("您有"+count+"项资产可以进行报废，请到报废页面进行处理");
		return m;
	}
	public List<Map<String,Object>> getAllassetsBygroup(){

		
		StringBuffer sb= new StringBuffer();
		sb.append("select * from(select e.* from (");
		sb.append("select t.a_name,t.a_class_name,t.a_unit_price, (select e.dept_name from tb_dept e where e.id=t.a_use_dept) a_use_dept,t.a_status,t.a_measure,")
			.append("count(t.a_name) as amount")
			.append(" from tb_assets t")
			.append(" where t.a_status not in ('删除','盘亏')")
			.append(" group by t.a_name,t.a_class_name,t.a_unit_price,t.a_use_dept,t.a_status,t.a_measure)").append("e  order by e.a_use_dept,e.a_name)");
		sb.append(" UNION all");
		sb.append("  select '小计', null, sum(b.a_unit_price), null, null, null,count(*) from tb_assets b where b.a_status not in ('删除')");
		
		//List<Map<String,Object>> list= this.jdbcTemplate.queryForList(sb.toString());
		return this.jdbcTemplate.queryForList(sb.toString());
	}
	public void temp() throws Exception{
//		StringBuffer sb= new StringBuffer();
//		sb.append("select t.a_name,t.a_class_name,t.a_unit_price, t.a_use_dept,t.a_status,")
//			.append("count(t.a_name) as amount")
//			.append(" from tb_assets t")
//			.append(" where t.a_status not in ('删除','盘亏')")
//			.append(" group by t.a_name,t.a_class_name,t.a_unit_price, t.a_use_dept,t.a_status");
//		List<Map<String,Object>> list= this.jdbcTemplate.queryForList(sb.toString());
//		System.out.println(list);
//		AssetsBean ab=this.assetsDao.get("402881e64d5c8e54014d5c98f6230034");
//
//		AssetsBean assetsBean = (AssetsBean) ab.clone();
//		assetsBean.setAssetsdetails_list(null);
//		assetsBean.setA_name(assetsBean.getA_name()+"复制的对象2");
//		this.assetsDao.save(assetsBean);
		
//		String hql ="from AssetsBean a where a.a_class_name='计算机设备及软件' and  a.a_name='主机箱' and  a.a_status='在用' and a.a_use_dept=(select b.id from DeptBean b where b.dept_name='工程部')";
//		List<AssetsBean> list =this.assetsDao.find(hql.toString(),null);
//		System.out.println("找到现有数量："+list.size());
		
		StringBuffer sql = new StringBuffer();
		//查询本月是否有盘点记录
		sql.append(" select count(*)");
		sql.append(" from tb_receipt a where");
		sql.append(" a.receipt_type='盘点单'");
		sql.append(" and to_char(to_date(a.receipt_data,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')");
		//System.out.println(this.jdbcTemplate.queryForObject(sql.toString(), Integer.class));
		Integer count=new Integer(0);
		count=this.jdbcTemplate.queryForObject(sql.toString(),Integer.class);
		System.out.println(count);
	}
	@SuppressWarnings("resource")
	public static void main(String[] args) throws Exception {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		AssetsService assetsService = (AssetsService) context.getBean("assetsService");
		assetsService.fold();

		
	}
	public Messages delAssets(String id) {
		Messages m= new Messages();
		AssetsBean assetsBean = this.assetsDao.get(id);
		assetsBean.setA_status("删除");
		this.assetsDao.update(assetsBean);
		m.setInfo(true);
		m.setText("删除成功");
		return m;
	}
	public Messages editassets(AssetsBean ab) {
		Messages m= new Messages();
		AssetsBean assetsBean = this.assetsDao.get(ab.getId());
		assetsBean.setA_class_name(ab.getA_class_name());
		assetsBean.setA_name(ab.getA_name());
		assetsBean.setA_specification(ab.getA_specification());
		assetsBean.setA_measure(ab.getA_measure());
		assetsBean.setA_machine_no(ab.getA_machine_no());
		assetsBean.setA_f_number(ab.getA_f_number());
		assetsBean.setA_remark(ab.getA_remark());
		
		this.assetsDao.update(assetsBean);
		m.setInfo(true);
		m.setText("修改成功");
		return m;
	}
	/**
	 * 获取部门下在用资产
	 * @param id
	 * @return
	 */
	public List<Map<String, Object>> getAssetsBydept(String id) {
		List<Map<String,Object>> list = null;
		System.out.println(id);
		StringBuffer sql=new StringBuffer("select t.id,(select d.dept_name from tb_dept d where d.id=t.a_use_dept) as dept_name,t.a_number,t.a_class_name,t.a_name,t.a_unit_price,t.a_specification,");
		sql.append("(select c.receipt_data from tb_receipt c where c.id in( ")
		   	.append("select a.receipt_id from tb_receipt_details a where a.assets_number=t.a_number) and c.receipt_type='出库单') as ref_date ")
		   	.append(" from tb_assets t where 1=1");
		if(isNull(id)){
			sql.append( " and t.a_use_dept='"+id+"' and t.a_status='在用'");
			
			list= this.jdbcTemplate.queryForList(sql.toString());
		}else{
			sql.append( " and t.a_status='在用' and t.a_use_dept is not null");
			
			list= this.jdbcTemplate.queryForList(sql.toString());
		}
		System.out.println(sql.toString());
		System.out.println(list);
		return list;
	}
}

